USE db_World

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('usp_withdrawMoney'))
BEGIN
    DROP PROCEDURE usp_withdrawMoney
END
GO

CREATE PROCEDURE usp_withdrawMoney(@AccountId INT, @Money MONEY)
AS
	DECLARE @NewBalance MONEY = (
		(SELECT account_balance
			FROM tbl_Accounts
				WHERE account_id = @AccountId)
		- @Money)
	IF (@NewBalance >= 0)
	BEGIN
		UPDATE tbl_Accounts
			SET account_balance = @NewBalance
			WHERE account_id = @AccountId
	END
	ELSE 
	BEGIN
		RAISERROR('The required amount is not available.', 15, 1)
	END
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('usp_depositMoney'))
BEGIN
	DROP PROCEDURE usp_depositMoney
END
GO

CREATE PROCEDURE usp_depositMoney(@AccountId INT, @Money MONEY)
AS
	UPDATE tbl_accounts 
	SET account_balance = account_balance + @Money
	WHERE account_id = @AccountId
GO

DECLARE @AccountId INT = 1
DECLARE @Money INT = 100000

SELECT account_balance FROM tbl_Accounts WHERE account_id = @AccountId;

BEGIN TRAN
BEGIN TRY
	EXEC usp_depositMoney @AccountId = @AccountId, @Money = @Money;
	SELECT account_balance FROM tbl_Accounts WHERE account_id = @AccountId;

	EXEC usp_withdrawMoney @AccountId = @AccountId, @Money = @Money;
	SELECT account_balance FROM tbl_Accounts WHERE account_id = @AccountId;

	COMMIT TRAN
END TRY
BEGIN CATCH
	ROLLBACK TRAN
END CATCH